package com.wanmait.myhouse.dao;

import com.wanmait.myhouse.util.DBHelper;
import com.wanmait.myhouse.vo.Mediator;
import com.wanmait.myhouse.vo.Pager;
import com.wanmait.myhouse.vo.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class UserDAO {

    public User findById(Integer id){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,user_name as userName , user_phone as userPhone from t_user where id=?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<>(User.class), id);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public User userByName(String userName){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,user_name as userName ,user_pass as userPass ,user_phone as userPhone from t_user where user_name=?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<>(User.class),userName);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public int getTotal(){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select count(1) as total from t_user";
        try {
            Object o = queryRunner.query(sql, new ScalarHandler<>());
            int total = Integer.parseInt(o.toString());
            return (int) total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    public List<User> findByPagen(Pager pager){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select *,user_name as userName , user_phone as userPhone from t_user order by userRegtime desc limit ?,?";
        int start = (pager.getPageNO()-1)*pager.getPageSize();
        try {
            List<User>userList = queryRunner.query(sql, new BeanListHandler<>(User.class), start, pager.getPageSize());
            return userList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }


    public void banUser(Integer id,Boolean ban){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "update t_user set userBan=? where id=?";
        try {
            queryRunner.update(sql,ban,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void add(User user){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());

        String sql = "insert into t_user (user_name ,user_pass,userBan,userRegtime)  values(?,?,?,?)";

        try {
            queryRunner.update(sql,user.getUserName(),user.getUserPass(),user.getUserBan(),user.getUserRegtime());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public User chashujv(User user){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());

        String sql = "select *,user_name as userName , user_phone as userPhone  from t_user where user_name = ? and user_pass = ?";
        try {
            User user1 = queryRunner.query(sql,new BeanHandler<>(User.class),user.getUserName(),user.getUserPass());
            return user1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;

    }
    public User FindByName(User user){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "select * from t_user where user_name=?";
        try {
            User user1 = queryRunner.query(sql, new BeanHandler<>(User.class),user.getUserName());
            return user1;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
    public void updateFindUser(User user){//修改用户信息
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "update t_user set  user_name=? ,user_pass=? ,user_phone=?, userEmail=? , userHead=? where id = ? ";
        try {
            queryRunner.update(sql,user.getUserName(),user.getUserPass(),user.getUserPhone(),user.getUserEmail(),user.getUserHead(),user.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public void updatePass(User user){
        QueryRunner queryRunner = new QueryRunner(DBHelper.getDruidDataSource());
        String sql = "update t_user set user_pass = ? where user_name =?";
        try {
            queryRunner.update(sql,user.getUserPass(),user.getUserName());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}
